package avicit.im.lantop.basic.fieldmapset.action.service.impl;

import avicit.cbb.tabledefine.service.CommTableManagerIntfsService;
import avicit.im.lantop.basic.fieldmapset.dao.LantopFieldMapSetManagerDao;
import avicit.im.lantop.basic.fieldmapset.domain.LantopConditionSet;
import avicit.im.lantop.basic.fieldmapset.domain.LantopFieldRelateSet;
import avicit.im.lantop.basic.fieldmapset.domain.LantopFieldRelateSetItem;
import avicit.im.lantop.basic.fieldmapset.service.LantopFieldMapSetService;
import avicit.im.lantop.basic.libstructuredefine.domain.LantopArchiveClassT;
import avicit.platform6.commons.utils.ComUtil;
import avicit.platform6.commons.utils.PojoUtil;
import avicit.platform6.commons.utils.web.TreeNode;
import avicit.platform6.core.dao.Paging;
import avicit.platform6.core.domain.BeanBase;
import avicit.platform6.core.exception.DaoException;
import avicit.platform6.core.jdbc.JdbcAvicit;
import avicit.platform6.core.jdbc.Page;
import avicit.platform6.core.properties.PlatformConstant.OpResult;
import avicit.platform6.core.properties.PlatformConstant.OpType;
import avicit.platform6.core.service.ServiceBase;
import avicit.platform6.core.session.SessionHelper;
import avicit.platform6.modules.system.syslog.service.LogBaseFactory;
import avicit.platform6.modules.system.syslog.service.SysLogUtil;
import com.github.pagehelper.Page;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>金航数码科技有限责任公司</p>
 * <p>作者：钟一华</p>
 * <p>邮箱：zhongyih@avicit.com</p>
 * <p>创建时间： 2015-11-3 下午2:15:39 </p>
 * <p>类说明：字段对应设置接口实现类</p>
 * <p>修改记录： </p>
 */
@Service
@SuppressWarnings({"unchecked", "rawtypes"})
public class LantopFieldMapSetServiceImpl extends ServiceBase implements LantopFieldMapSetService {

	@Resource
    JdbcTemplate jdbcTemplate;
	@Autowired
	private JdbcAvicit jdbcAvicit;

	@Autowired(required = true)
	private CommTableManagerIntfsService commTableManagerIntfsService;

	@Autowired(required = true)
	LantopFieldMapSetManagerDao lantopFieldMapSetManagerDao;

	@Override
	public void delete(BeanBase arg0) throws DaoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void insert(BeanBase arg0) throws DaoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void update(BeanBase arg0) throws DaoException {
		// TODO Auto-generated method stub

	}

	/**
	 * 根据当前人对应组织构建根节点
	 * 
	 * @author 朱锋
	 * @param deptId
	 * @return
	 * @throws Exception
	 *             LantopArchiveClassT
	 * @throws
	 */
	@Override
	public LantopArchiveClassT getRootLantopArchiveTabClassVByType(String deptId) throws Exception {
		// 获取当前人对应的组织Id
		StringBuffer sbSqlDept = new StringBuffer();
		sbSqlDept.append("SELECT T.ORG_ID FROM SYS_DEPT T WHERE T.ID = '" + deptId + "'");
		List listDept = this.hibernateDao.findBySQL(sbSqlDept.toString().toString());
		String orgId = "";
		if (listDept.size() != 0) {
			orgId = String.valueOf(listDept.get(0));
		}
		StringBuffer sbSqlOrg = new StringBuffer();
		sbSqlOrg.append("SELECT T.ID, T.ORG_CODE, T.ORG_NAME FROM BPM_DEF_ORG_V T WHERE T.ID = '" + orgId + "'");
		List listOrg = this.hibernateDao.findBySQL(sbSqlOrg.toString());
		LantopArchiveClassT lantopArchiveClass = new LantopArchiveClassT();
		if (listOrg.size() != 0) {
			Object[] obj = (Object[]) listOrg.get(0);
			String uuId = ComUtil.getId();
			lantopArchiveClass.setId(uuId);
			lantopArchiveClass.setOrgId(orgId);
			lantopArchiveClass.setDeptId(deptId);
			lantopArchiveClass.setSysId("LANTOP");
			lantopArchiveClass.setArchiveClassNodeCode(obj[1] + "");
			lantopArchiveClass.setArchiveClassNodeName(obj[2] + "");
			lantopArchiveClass.setParentId("ROOT");
			lantopArchiveClass.setArchiveClassNodeType("-1");
			lantopArchiveClass.setArchiveClassNodePath(uuId);
			PojoUtil.setSysProperties(lantopArchiveClass, OpType.insert);
			this.hibernateDao.save(lantopArchiveClass);
			SysLogUtil.log(lantopArchiveClass, null, OpType.insert, OpResult.success);
		}
		return lantopArchiveClass;
	}

	/**
	 * 判断是否存在根节点
	 * 
	 * @author 朱锋
	 * @param deptId
	 * @return int
	 * @throws
	 */
	@Override
	public int getParentNodeByParentId(String deptId) {
		// 获取当前人对应的组织Id
		StringBuffer sbSqld = new StringBuffer();
		sbSqld.append("SELECT T.ORG_ID FROM SYS_DEPT T WHERE T.ID = '" + deptId + "'");
		List list1 = this.hibernateDao.findBySQL(sbSqld.toString());
		String orgId = "";
		if (list1.size() != 0) {
			orgId = String.valueOf(list1.get(0));
		}
		StringBuffer sbSqlc = new StringBuffer();
		sbSqlc.append("SELECT COUNT(*)\n");
		sbSqlc.append("  FROM LANTOP_ARCHIVE_CLASS T\n");
		sbSqlc.append(" WHERE T.ORG_ID = '" + orgId + "'\n");
		sbSqlc.append("   AND T.PARENT_ID = 'ROOT'");
		List<Object> list = this.hibernateDao.findBySQL(sbSqlc.toString());
		int returnCount = 0;
		if (list.size() != 0) {
			returnCount = Integer.parseInt(String.valueOf(list.get(0)));
		}
		return returnCount;

	}

	/**
	 * 递归获取Tree当前节点下数据
	 * 
	 * @author 朱锋
	 * @param id
	 * @param level
	 * @return List<TreeNode>
	 * @throws
	 */
	@Override
	public List<TreeNode> recurFindTreeNodesByParentId(String id, int level) {
		// 递归一层减一层
		List<TreeNode> nodes = new ArrayList<TreeNode>();
		// 查询业务数据
		List<Map<String, Object>> childTreeList = findNodesByParentId(id);
		// 将业务数据格式转换成esayui树形格式
		for (Map<String, Object> row : childTreeList) {
			String rid = (String) row.get("ID");
			String rname = (String) row.get("ARCHIVE_CLASS_NODE_NAME");
			TreeNode node = new TreeNode();
			node.setId(rid);
			node.setText(rname);
			node.setAttributes(row);
			node.set_parentId((String) row.get("PARENT_ID"));
			BigDecimal childCount2 = BigDecimal.valueOf(Long.valueOf(row.get("CHILD_COUNT") + ""));
			if (null != childCount2 && childCount2.longValue() > 0) {
				if (level > 0) {
					List<TreeNode> cnodes = recurFindTreeNodesByParentId(rid, level);
					node.getChildren().addAll(cnodes);
					node.setState("open");
				} else {
					node.setState("closed");
				}
			} else {
				node.setState("open");
			}
			nodes.add(node);
		}
		return nodes;
	}

	/**
	 * 获取子节点
	 * 
	 * @param parentId
	 * @return
	 */
	public List<Map<String, Object>> findNodesByParentId(String parentId) {
		Assert.notNull(parentId, "参数不能为空！");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT (SELECT COUNT(*)\n");
		sbSql.append("          FROM LANTOP_ARCHIVE_TAB_CLASS_V D2\n");
		sbSql.append("         WHERE D2.PARENT_ID = D1.ID) CHILD_COUNT,\n");
		sbSql.append("       D1.*\n");
		sbSql.append("  FROM LANTOP_ARCHIVE_TAB_CLASS_V D1\n");
		sbSql.append(" WHERE D1.PARENT_ID =?");
		return this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString(), parentId);
	}

	/**
	 * 获取字段对应设置数据
	 * 
	 * @author 朱锋
	 * @param page
	 * @param parameter
	 * @return Page<Map<String,Object>>
	 * @throws
	 */
	@Override
	public Page<Map<String, Object>> getPageRelateSetList(Paging<LantopFieldRelateSet> page, Map<String, Object> parameter) throws Exception {
		String sourceTableId = (String) parameter.get("sourceTableId");
		String orgId = (String) parameter.get("orgId");
		// SanXiaFiles create by zhaoxiaolong 查询当前原库表是否为外部全宗的
		StringBuffer searchOutTableSql = new StringBuffer();
		StringBuffer sbSql = new StringBuffer();
		int count = 0;
		searchOutTableSql.append(" SELECT TABLE_NAME FROM CBB_TABLES WHERE ID='"+sourceTableId+"' "); // 排除虚拟表公共字段，因为公共字段这个表对除三峡集团外的其他全宗默认为外部表
		List<Map<String,Object>> queryForList = this.jdbcAvicit.getJdbcTemplate().queryForList(searchOutTableSql.toString());
		if (queryForList!=null&&queryForList.size()>0){
			String tableName = queryForList.get(0).get("TABLE_NAME").toString();
			if (tableName!=""&&tableName.equals("LANTOP_PUB_FIELD")){
				count = 1;
			}
		}
		if (count==0){ // 只有外部业务归档接口的表是0，本地表为1
			searchOutTableSql = new StringBuffer();
			searchOutTableSql.append(" SELECT COUNT(1) FROM CBB_TABLES WHERE ID='"+sourceTableId+"' AND ORG_ID='"+orgId+"' ");
			count = this.jdbcAvicit.getJdbcTemplate().queryForObject(searchOutTableSql.toString(),Integer.class);
		}
		sbSql.append("WITH T1 AS ( ");
		sbSql.append(" SELECT T.ID,\n");
		sbSql.append("       T.ORG_ID,\n");
		sbSql.append("       T.DEPT_ID,\n");
		sbSql.append("       T.SYS_ID,\n");
		sbSql.append("       T.SOURCE_TABLE_ID,\n");
		sbSql.append("       T.SOURCE_TABLE_NAME,\n");
		sbSql.append("       T.SOURCE_TABLE_TITLE,\n");
		sbSql.append("       T.TGT_TABLE_ID,\n");
		sbSql.append("       T.TGT_TABLE_NAME,\n");
		sbSql.append("       T.TGT_TABLE_TITLE,\n");
		sbSql.append("       T.TGT_ARCHIVE_CLASS_ID,\n");
		sbSql.append("       LAC.ARCHIVE_CLASS_NODE_CODE,\n");
		sbSql.append("       LAC.ARCHIVE_CLASS_NODE_NAME,\n");
		sbSql.append("       CT.TABLE_NAME AS TGT_TABLE_NAME_EN,\n");
		sbSql.append("       CT.TABLE_IS_TRUE_TABLE AS TGT_TABLE_IS_TRUE_TABLE\n");
		sbSql.append("  FROM LANTOP_FIELD_RELATE_SET T\n");
		sbSql.append("  LEFT JOIN LANTOP_ARCHIVE_CLASS LAC\n");
		sbSql.append("    ON LAC.ID = T.TGT_ARCHIVE_CLASS_ID\n");
		sbSql.append("    LEFT JOIN CBB_TABLES CT\n");
		sbSql.append("     ON CT.ID = T.TGT_TABLE_ID\n");
		sbSql.append(" WHERE 1 = 1\n");
		if (!"".equals(sourceTableId) && null != sourceTableId) {
			sbSql.append(" AND CT.ORG_ID='"+orgId+"' ");
			sbSql.append("   AND T.SOURCE_TABLE_ID = '" + sourceTableId + "'\n");
			sbSql.append(" OR T.TGT_TABLE_ID = '" + sourceTableId + "'\n");
		}
		sbSql.append(" AND T.ORG_ID = '" + orgId + "' ");
		sbSql.append("  ) ");
		if (count==0){
			sbSql.append(" SELECT\n" +
					"\tT1.*,\n" +
					"\tSOV.ORG_NAME  TGT_TABLE_NAME_NEW \n" +
					"FROM\n" +
					"\tT1\n" +
					"\tLEFT JOIN CBB_TABLES CT ON T1.TGT_TABLE_ID=CT.ID LEFT JOIN SYS_ORG_V SOV ON CT.ORG_ID = SOV.ID WHERE CT.ORG_ID='"+orgId+"'  ");
		}else {
			sbSql.append(" SELECT\n" +
					"\tT1.*,\n" +
					"\tSOV.ORG_NAME  TGT_TABLE_NAME_NEW \n" +
					"FROM\n" +
					"\tT1\n" +
					"\tLEFT JOIN  SYS_ORG_V SOV ON T1.ORG_ID = SOV.ID WHERE T1.ORG_ID='"+orgId+"'  ");
		}
		Page<Map<String, Object>> pageBean = new Page<Map<String, Object>>(page.getPageNo(), page.getPageSize());
		jdbcAvicit.getDatesByPageForMap(pageBean, sbSql.toString(), parameter, null);
		return pageBean;
	}

	/**
	 * 获取条件设置数据
	 * 
	 * @author 朱锋
	 * @param page
	 * @param parameter
	 * @return Page<Map<String,Object>>
	 * @throws
	 */
	@Override
	public Page<Map<String, Object>> getPageRConditionSetList(Paging<LantopConditionSet> page, Map<String, Object> parameter) throws Exception {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT T.LANTOP_FIELD_RELATE_SET_ID,\n");
		sbSql.append("       T.ORG_ID,\n");
		sbSql.append("       T.DEPT_ID,\n");
		sbSql.append("       T.SYS_ID,\n");
		sbSql.append("       T.SOURCE_TABLE_ID,\n");
		sbSql.append("       T.SOURCE_TABLE_NAME,\n");
		sbSql.append("       T.SOURCE_TABLE_TITLE,\n");
		sbSql.append("       T.TGT_TABLE_ID,\n");
		sbSql.append("       T.TGT_TABLE_NAME,\n");
		sbSql.append("       T.TGT_TABLE_TITLE,\n");
		sbSql.append("       T.TGT_ARCHIVE_CLASS_ID,\n");
		sbSql.append("       T.SIGN_WHERE_SQL,\n");
		sbSql.append("       T.BATCH_WHERE_SQL,\n");
		sbSql.append("       T.WHERE_SQL_CN\n");
		sbSql.append("  FROM LANTOP_CONDITION_SET_V T");
		Page<Map<String, Object>> pageBean = new Page<Map<String, Object>>(page.getPageNo(), page.getPageSize());
		jdbcAvicit.getDatesByPageForMap(pageBean, sbSql.toString(), parameter, null);
		return pageBean;
	}

	/**
	 * 获取子节点
	 * 
	 * @param
	 * @return
	 */
	@Override
    public List<Map<String, Object>> getArchiveClassName(String id) {
		Assert.notNull(id, "参数不能为空！");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT *\n");
		sbSql.append("  FROM LANTOP_ARCHIVE_CLASS T\n");
		sbSql.append(" WHERE T.ARCHIVE_CLASS_NODE_TYPE = '2' AND T.ID = '" + id + "'\n");
		return this.jdbcAvicit.queryForMap(sbSql.toString(), null);
	}

	/**
	 * 删除字段对应设置
	 * 
	 * @throws Exception
	 */
	@Override
	public void deleteMapSet(String ids) throws Exception {
		Assert.notNull(ids);
		String[] idArray = ids.split(";");
		for (int i = 0; i < idArray.length; i++) {
			// 删除子表操作
			StringBuffer sbSqli = new StringBuffer();
			sbSqli.append("DELETE FROM LANTOP_FIELD_RELATE_SET_ITEM T\n");
			sbSqli.append(" WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + idArray[i] + "'");
			this.jdbcAvicit.getJdbcTemplate().update(sbSqli.toString());
			SysLogUtil.log("字段对应设置模块", "删除字段对应设置详细记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
			// 删除主表操作
			StringBuffer sbSql = new StringBuffer();
			sbSql.append("DELETE FROM LANTOP_FIELD_RELATE_SET T WHERE T.ID = '" + idArray[i] + "'");
			this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
			SysLogUtil.log("字段对应设置模块", "删除字段对应设置记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
		}
	}

	/**
	 * 删除条件设置信息
	 * 
	 * @throws Exception
	 */
	@Override
	public void deleteConditionSet(String ids) throws Exception {
		Assert.notNull(ids);
		String[] idArray = ids.split(";");
		for (int i = 0; i < idArray.length; i++) {
			StringBuffer sbSql = new StringBuffer();
			sbSql.append("DELETE FROM LANTOP_CONDITION_SET T WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + idArray[i] + "'");
			this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
			SysLogUtil.log("字段对应设置模块", "删除条件设置记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
			// 级联更新字段对应设置主表SQL
			StringBuffer upSql = new StringBuffer();
			upSql.append("UPDATE LANTOP_FIELD_RELATE_SET T\n");
			upSql.append("   SET T.SIGN_WHERE_SQL = '', T.BATCH_WHERE_SQL = '', T.WHERE_SQL_CN = ''\n");
			upSql.append(" WHERE T.ID = '" + idArray[i] + "'");
			this.jdbcAvicit.getJdbcTemplate().update(upSql.toString());
			SysLogUtil.log("字段对应设置模块", "更新字段对应设置记录", OpType.update, OpResult.success, LogBaseFactory.getLogBase());
		}
	}

	/**
	 * 查询对应设置主表信息ID
	 * 
	 * @author 朱锋
	 * @param myTableId
	 * @param elseTableId
	 * @param tgtNodeId
	 * @param sysId
	 * @param tgtOrsource
	 * @return
	 * @throws Exception
	 *             String
	 * @throws
	 */
	@Override
	public String getRelateSetIdByIds(String myTableId, String elseTableId, String tgtNodeId, String sysId, String tgtOrsource) throws Exception {
		String relateSetId = "";
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT T.ID\n");
		sbSql.append("  FROM LANTOP_FIELD_RELATE_SET T\n");
		sbSql.append(" WHERE T.SYS_ID = '" + sysId + "'\n");
		if ("tgt".equals(tgtOrsource)) {
			sbSql.append("   AND T.SOURCE_TABLE_ID = '" + elseTableId + "'\n");
			sbSql.append("   AND T.TGT_TABLE_ID = '" + myTableId + "'\n");
		} else if ("source".equals(tgtOrsource)) {
			sbSql.append("   AND T.SOURCE_TABLE_ID = '" + myTableId + "'\n");
			sbSql.append("   AND T.TGT_TABLE_ID = '" + elseTableId + "'\n");
		}
		if (!"".equals(tgtNodeId) && !"null".equals(tgtNodeId) && null != tgtNodeId) {
			sbSql.append("   AND T.TGT_ARCHIVE_CLASS_ID = '" + tgtNodeId + "'");
		} else {
			sbSql.append("   AND T.TGT_ARCHIVE_CLASS_ID IS NULL");
		}
		List<Object> list = this.hibernateDao.findBySQL(sbSql.toString());
		if (list.size() != 0) {
			relateSetId = String.valueOf(list.get(0));
		}
		return relateSetId;
	}

	/**
	 * 新增字段对应设置主表信息
	 * 
	 * @author 朱锋
	 * @param relateSetId
	 * @param sourceTableId
	 * @param tgtTableId
	 * @param tgtNodeId
	 * @param sysId
	 * @throws Exception
	 *             void
	 * @throws
	 */
	@Override
	public void insertRelateSet(String relateSetId, String sourceTableId, String tgtTableId, String tgtNodeId, String sysId, String type) throws Exception {
		List<Map<String, Object>> sourceList = this.commTableManagerIntfsService.getCbbTableAndColumnsByTabIdByIntfs(sourceTableId);
		List<Map<String, Object>> tgtList = new ArrayList<Map<String, Object>>();
		tgtList = this.commTableManagerIntfsService.getCbbTableAndColumnsByTabIdByIntfs(tgtTableId);
		String sourceTableName = "";
		String sourceTableTitle = "";
		if (sourceList.size() > 0) {
			sourceTableName = String.valueOf(sourceList.get(0).get("TABLE_NAME"));
			sourceTableTitle = String.valueOf(sourceList.get(0).get("TABLE_TITLE"));
		}
		String tgtTableName = "";
		String tgtTableTitle = "";
		if (tgtList.size() > 0) {
			tgtTableName = String.valueOf(tgtList.get(0).get("TABLE_NAME"));
			tgtTableTitle = String.valueOf(tgtList.get(0).get("TABLE_TITLE"));
		}
		LantopFieldRelateSet lantopFieldRelateSet = new LantopFieldRelateSet();
		lantopFieldRelateSet.setId(relateSetId);
		lantopFieldRelateSet.setOrgId(SessionHelper.getCurrentOrgId());
		lantopFieldRelateSet.setDeptId(SessionHelper.getCurrentDeptId());
		lantopFieldRelateSet.setSysId(sysId);
		lantopFieldRelateSet.setSourceTableId(sourceTableId);
		Map<String, Object> sourceParam = new HashMap<String, Object>();
		sourceParam.put("TABLE_ID", sourceTableId);
		String sourceArchiveType = this.getLantopArchiveTypeByTableId(sourceParam);
		lantopFieldRelateSet.setAttribute01(sourceArchiveType);
		lantopFieldRelateSet.setSourceTableName(sourceTableName);
		lantopFieldRelateSet.setSourceTableTitle(sourceTableTitle);
		lantopFieldRelateSet.setTgtTableId(tgtTableId);
		Map<String, Object> targetParam = new HashMap<String, Object>();
		targetParam.put("TABLE_ID", tgtTableId);
		String targetArchiveType = this.getLantopArchiveTypeByTableId(targetParam);
		lantopFieldRelateSet.setAttribute02(targetArchiveType);
		lantopFieldRelateSet.setTgtTableName(tgtTableName);
		lantopFieldRelateSet.setTgtTableTitle(tgtTableTitle);
		if (!"".equals(tgtNodeId)) {
			lantopFieldRelateSet.setTgtArchiveClassId(tgtNodeId);
		}
		PojoUtil.setSysProperties(lantopFieldRelateSet, OpType.insert);
		this.hibernateDao.save(lantopFieldRelateSet);
		SysLogUtil.log(lantopFieldRelateSet, null, OpType.insert, OpResult.success);
	}

	/**
	 * 新增字段对应信息
	 * 
	 * @author 朱锋
	 * @param rowDatas
	 * @param relateSetId
	 * @param
	 * @param
	 * @param sysId
	 * @throws Exception
	 *             void
	 * @throws
	 */
	@Override
	public void insertRelateSetItem(String rowDatas, String relateSetId, String sysId) throws Exception {
		// 根据指标对应ID，删除已存在的数据
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("DELETE FROM LANTOP_FIELD_RELATE_SET_ITEM T\n");
		sbSql.append(" WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + relateSetId + "'");
		jdbcAvicit.getJdbcTemplate().execute(sbSql.toString());
		SysLogUtil.log("字段对应设置模块", "删除字段对应设置详细记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
		// 新增字段对应设置信息
		if (rowDatas != null) {
			JSONArray ja = JSONArray.fromObject(rowDatas);
			for (int i = 0; i < ja.size(); i++) {
				JSONObject jj = JSONObject.fromObject(ja.get(i));
				String sourceColId = jj.getString("ATTRIBUTE_03");
				if(null != sourceColId && !"".equals(sourceColId) && !"null".equals(sourceColId)){
					List<Map<String, Object>> listColumns = this.commTableManagerIntfsService.getCbbColumnsByColIdByIntfs(sourceColId);
					if (listColumns.size() > 0) {
						LantopFieldRelateSetItem lantopFieldRelateSetItem = new LantopFieldRelateSetItem();
						lantopFieldRelateSetItem.setId(ComUtil.getId());
						lantopFieldRelateSetItem.setOrgId(SessionHelper.getCurrentOrgId());
						lantopFieldRelateSetItem.setDeptId(SessionHelper.getCurrentDeptId());
						lantopFieldRelateSetItem.setSysId(sysId);
						lantopFieldRelateSetItem.setLantopFieldRelateSetId(relateSetId);
						lantopFieldRelateSetItem.setSourceFieldNameEn(String.valueOf(listColumns.get(0).get("COL_NAME")));
						lantopFieldRelateSetItem.setSourceFieldNameCn(String.valueOf(listColumns.get(0).get("COL_LABEL")));
						lantopFieldRelateSetItem.setSourceFieldType(String.valueOf(listColumns.get(0).get("COL_TYPE")));
						lantopFieldRelateSetItem.setSourceFieldLength(String.valueOf(listColumns.get(0).get("COL_LENGTH")));
						lantopFieldRelateSetItem.setTgtFieldNameEn(jj.getString("COL_NAME"));
						lantopFieldRelateSetItem.setTgtFieldNameCn(jj.getString("COL_LABEL"));
						lantopFieldRelateSetItem.setTgtFieldType(jj.getString("COL_TYPE"));
						lantopFieldRelateSetItem.setTgtFieldLength(jj.getString("COL_LENGTH"));
						PojoUtil.setSysProperties(lantopFieldRelateSetItem, OpType.insert);
						this.hibernateDao.save(lantopFieldRelateSetItem);
						SysLogUtil.log(lantopFieldRelateSetItem, null, OpType.insert, OpResult.success);
					}
				}
			}
		}
	}
	
	/**
	 * 根据tableId获取该表档案类型
	 * @param param
	 * @return
	 * @throws Exception String
	 * @throws
	 */
	public String getLantopArchiveTypeByTableId(Map<String, Object> param) throws Exception {
		String tableId = (String) param.get("TABLE_ID");
		String sql = "SELECT * FROM CBB_TAB_NODE T WHERE T.TABLE_ID = '" + tableId + "'";
		List<Map<String, Object>> list = this.jdbcAvicit.getJdbcTemplate().queryForList(sql);
		String archiveType = "";
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			String nodeId = (String) map.get("NODE_ID"); 
			StringBuffer sbSql = new StringBuffer();
			sbSql.append("SELECT * FROM LANTOP_ARCHIVE_CLASS T\n");
			sbSql.append("CONNECT BY PRIOR T.PARENT_ID = T.ID\n");
			sbSql.append("START WITH ID = '" + nodeId + "'"); 
			List<Map<String, Object>> classList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
			for (int j = 0; j < classList.size(); j++) {
				Map<String, Object> classMap = classList.get(j);
				Object at = classMap.get("ARCHIVE_TYPE");
				if (null != at && !"".equals(at) && !"0".equals(at)) {
					archiveType = (String) at;
					break;
				}
			}
		}
		return archiveType;
	}

	/**
	 * 获取源表列中文名,英文名
	 * 
	 * @author 朱锋
	 * @param relateSetId
	 * @param
	 * @return
	 * @throws Exception
	 *             String[]
	 * @throws
	 */
	@Override
	public List<Object[]> getSourceColByRidAndTcn(String relateSetId, String tnameEn) throws Exception {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT T.SOURCE_FIELD_NAME_EN, T.SOURCE_FIELD_NAME_CN\n");
		sbSql.append("  FROM LANTOP_FIELD_RELATE_SET_ITEM T\n");
		sbSql.append(" WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + relateSetId + "'\n");
		sbSql.append("   AND T.TGT_FIELD_NAME_EN = '" + tnameEn + "'");
		List<Object[]> list = this.hibernateDao.findBySQL(sbSql.toString());
		return list;
	}

	/**
	 * 判断是否已经存在物理表节点的条件设置记录
	 * 
	 * @author 朱锋
	 * @param id
	 * @return
	 * @throws Exception
	 *             String
	 * @throws
	 */
	@Override
	public boolean getFlagConditionSet(String id) throws Exception {
		boolean flag = true;
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT COUNT(*)\n");
		sbSql.append("  FROM LANTOP_CONDITION_SET_V T\n");
		sbSql.append(" WHERE T.SOURCE_TABLE_ID = '" + id + "'");
		List<Object> list = this.hibernateDao.findBySQL(sbSql.toString());
		if (list.size() != 0) {
			int num = Integer.parseInt(String.valueOf(list.get(0)));
			if (num == 0) {
				flag = false;
			}
		}
		return flag;
	}

	/**
	 * 获取档案分类树信息：树形展示（文件夹、模板、数据节点、物理表） 通过id,orgid,deptid获得树节点数据
	 * 
	 * @author 朱锋
	 * @param id
	 * @param orgId
	 * @param deptId
	 * @return List<TreeNode>
	 * @throws
	 */
	public List<TreeNode> getTreeNodeByOrgIdDeptId(String id, String orgId, String deptId) throws Exception {
		List<TreeNode> nodeList = new ArrayList<TreeNode>();
		List<Map<String, Object>> archiveClassList = this.findNodesByParentIdByCondition(orgId, id);
		// 将业务数据格式转换成esayui树形格式，此处封装了一个TreeNodeVo节点
		for (Map<String, Object> row : archiveClassList) {
			String rid = (String) row.get("id");
			String rname = (String) row.get("archive_class_node_name");
			String rOrgId = (String) row.get("org_id");
			String rDeptId = (String) row.get("dept_id");
			TreeNode node = new TreeNode();
			node.setId(rid);
			node.setText(rname);
			String archiveType = (String) row.get("ARCHIVE_TYPE");
			String nodeType = (String) row.get("ARCHIVE_CLASS_NODE_TYPE");
			String rootId = (String) row.get("PARENT_ID");
			// 根据节点类型,设置节点图标
			if (null != rootId && "ROOT".equals(rootId)) { // 根节点
				node.setIconCls("icon-im-newnorg");
			} else if (null != archiveType && "-1".equals(archiveType) && null == nodeType) { // 档案类型 -1 物理表
				node.setIconCls("icon-im-newntable");
			} else if (null != archiveType && "0".equals(archiveType) && null == nodeType) { // 0 模版文件夹
				node.setIconCls("icon-im-newnmodulefile");
			} else if (null != archiveType && "1".equals(archiveType) && null == nodeType) { // 1 标识文件
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "2".equals(archiveType) && null == nodeType) { // 2 档案
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "3".equals(archiveType) && null == nodeType) { // 3 专题库
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "4".equals(archiveType) && null == nodeType) { // 4 多媒体
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "5".equals(archiveType) && null == nodeType) { // 5 项目归档计划
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "6".equals(archiveType) && null == nodeType) { // 6 管务
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "7".equals(archiveType) && null == nodeType) { // 7 其他
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "10".equals(archiveType) && null == nodeType) { // 10 虚拟表
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != nodeType && "1".equals(nodeType)) { // 节点类型 1 文件夹
				node.setIconCls("icon-im-newnmodulefile");
			} else if (null != nodeType && "2".equals(nodeType)) { // 数据节点
				node.setIconCls("icon-im-newndatanode");
			}
			node.setAttributes(row);
			BigDecimal childCount2 = BigDecimal.valueOf(Long.valueOf(row.get("CHILD_COUNT") + ""));
			if (null != childCount2 && childCount2.longValue() > 0) {
				List<TreeNode> cnodes = this.getTreeNodeByOrgIdDeptId(rid, rOrgId, rDeptId);
				node.getChildren().addAll(cnodes);
				node.setState("open");
			} else {
				node.setState("open");
			}
			nodeList.add(node);
		}
		return nodeList;
	}

	/**
	 * 获取子节点,对当前登陆用户的组织id和部门id进行过滤,只查询当前挡路用户组织和部门下的节点
	 * 获取档案分类树信息：树形展示（文件夹、模板、数据节点、物理表） 被调用方法getTreeNodeByOrgIdDeptId();
	 * 
	 * @param parentId
	 * @return 子节点集合
	 */
	public List<Map<String, Object>> findNodesByParentIdByCondition(String orgId, String parentId) {
		if ("".equals(parentId)) {
			parentId = "ROOT";
		}
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select (select count(*)\n");
		sbSql.append("from LANTOP_ARCHIVE_NODE_TAB_V LAC2\n");
		sbSql.append("where LAC2.PARENT_ID = LAC1.ID\n");
		sbSql.append("and LAC2.ORG_ID = ?) CHILD_COUNT,\n");
		sbSql.append("LAC1.*\n");
		sbSql.append("from LANTOP_ARCHIVE_NODE_TAB_V LAC1\n");
		sbSql.append("where LAC1.PARENT_ID = ?\n");
		sbSql.append("order by LAC1.NODE_SORT_NUMBER asc");
		Object[] objs = new Object[2];
		objs[0] = orgId;
		objs[1] = parentId;
		List<Map<String, Object>> list = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString(), objs);
		return list;
	}

	/**
	 * 获取档案分类树信息：树形展示（文件夹、模板、数据节点、物理表）
	 * 
	 * @author 朱锋
	 * @param orgId
	 * @param deptId
	 * @return
	 * @throws Exception
	 *             List<TreeNode>
	 * @throws
	 */
	@Override
	public List<TreeNode> getArchiveClassTreeNodeToTableInfoByCondition(String orgId, String deptId) throws Exception {
		List<TreeNode> nodes = this.getTreeNodeByOrgIdDeptId("", orgId, deptId);
		return nodes;
	}

	/**
	 * 新增条件设置信息
	 * 
	 * @author 朱锋
	 * @param json
	 * @param sysId
	 * @param lantopFieldRelateSetId
	 * @return
	 * @throws Exception
	 *             Map<String,String>
	 * @throws
	 */
	@Override
	public Map<String, String> insertConditionSet(String json, String sysId, String lantopFieldRelateSetId) throws Exception {
		Map<String, String> map = new HashMap<String, String>();
		String conditionEn = "";
		String conditionCn = "";
		// 解析JSON串
		JSONArray ja = JSONArray.fromObject(json);
		for (int i = 0; i < ja.size(); i++) {
			JSONObject jj = JSONObject.fromObject(ja.get(i));
			String leftBracket = jj.getString("0");// 左括号
			if (!"".equals(leftBracket) && null != leftBracket) {
				conditionEn = conditionEn + leftBracket;
			}
			String fieldNameEn = jj.getString("1");// 字段英文名
			if (!"".equals(fieldNameEn) && null != fieldNameEn) {
				conditionEn = conditionEn + fieldNameEn;
			}
			String fieldNameCn = jj.getString("6");// 字段中文名
			if (!"".equals(fieldNameCn) && null != fieldNameCn) {
				conditionCn = conditionCn + fieldNameCn;
			}
			String filedValue = jj.getString("3");// 字段值
			String operCharacter = jj.getString("2");// 运算符
			if (!"".equals(operCharacter) && null != operCharacter) {
				if ("DY".equals(operCharacter)) {
					conditionEn = conditionEn + " = ''" + filedValue + "''";
					conditionCn = conditionCn + " = ''" + filedValue + "''";
				} else if ("BDY".equals(operCharacter)) {
					conditionEn = conditionEn + " <> ''" + filedValue + "''";
					conditionCn = conditionCn + " <> ''" + filedValue + "''";
				} else if ("BH".equals(operCharacter)) {
					conditionEn = conditionEn + " IN (''" + filedValue + "'') ";
					conditionCn = conditionCn + " IN ''" + filedValue + "'' ";
				} else if ("BBH".equals(operCharacter)) {
					conditionEn = conditionEn + " NOT IN (''" + filedValue + "'') ";
					conditionCn = conditionCn + " NOT IN ''" + filedValue + "'' ";
				} else if ("ZBH".equals(operCharacter)) {
					conditionEn = conditionEn + " LIKE ''" + filedValue + "%'' ";
					conditionCn = conditionCn + " LIKE ''" + conditionCn + "%'' ";
				} else if ("YBH".equals(operCharacter)) {
					conditionEn = conditionEn + " LIKE ''%" + filedValue + "''";
					conditionCn = conditionCn + " LIKE ''%" + filedValue + "''";
				} else if ("DAY".equals(operCharacter)) {
					conditionEn = conditionEn + " > ''" + filedValue + "''";
					conditionCn = conditionCn + " > ''" + filedValue + "''";
				} else if ("DYDY".equals(operCharacter)) {
					conditionEn = conditionEn + " >= ''" + filedValue + "''";
					conditionCn = conditionCn + " >= ''" + filedValue + "''";
				} else if ("XY".equals(operCharacter)) {
					conditionEn = conditionEn + " < ''" + filedValue + "''";
					conditionCn = conditionCn + " < ''" + filedValue + "''";
				} else if ("XYDY".equals(operCharacter)) {
					conditionEn = conditionEn + " <= ''" + filedValue + "''";
					conditionCn = conditionCn + " <= ''" + filedValue + "''";
				} else if ("WK".equals(operCharacter)) {
					conditionEn = conditionEn + " IS NULL ";
					conditionCn = conditionCn + " IS NULL ";
				} else if ("BWK".equals(operCharacter)) {
					conditionEn = conditionEn + " IS NOT NULL ";
					conditionCn = conditionCn + " IS NOT NULL ";
				}
			}
			String rightBracket = jj.getString("4");// 右括号
			if (!"".equals(rightBracket) && null != rightBracket) {
				conditionEn = conditionEn + ")";
			}
			String logicOper = jj.getString("5");// 逻辑次（或者，并且）
			if (!"".equals(logicOper) && null != logicOper) {
				if ("OR".equals(logicOper)) {
					conditionEn = conditionEn + " OR ";
					conditionCn = conditionCn + " 或者 ";
				} else if ("AND".equals(logicOper)) {
					conditionEn = conditionEn + " AND ";
					conditionCn = conditionCn + " 并且 ";
				}
			}
			LantopConditionSet lantopConditionSet = new LantopConditionSet();
			lantopConditionSet.setId(ComUtil.getId());
			lantopConditionSet.setOrgId(SessionHelper.getCurrentOrgId());
			lantopConditionSet.setDeptId(SessionHelper.getCurrentDeptId());
			lantopConditionSet.setSysId(sysId);
			lantopConditionSet.setLantopFieldRelateSetId(lantopFieldRelateSetId);
			lantopConditionSet.setLeftBracket(leftBracket);
			lantopConditionSet.setFieldNameEn(fieldNameEn);
			lantopConditionSet.setFieldNameCn(fieldNameCn);
			lantopConditionSet.setOperCharacter(operCharacter);
			lantopConditionSet.setFiledValue(filedValue);
			lantopConditionSet.setRightBracket(rightBracket);
			lantopConditionSet.setLogicOper(logicOper);
			PojoUtil.setSysProperties(lantopConditionSet, OpType.insert);
			this.hibernateDao.save(lantopConditionSet);
			SysLogUtil.log(lantopConditionSet, null, OpType.insert, OpResult.success);
		}
		map.put("conditionEn", conditionEn);
		map.put("conditionCn", conditionCn);
		return map;
	}

	/**
	 * 根据字段对应设置ID，删除条件设置信息
	 * 
	 * @author 朱锋
	 * @param lantopFieldRelateSetId
	 * @throws Exception
	 *             void
	 * @throws
	 */
	@Override
	public void deleteConditionSetByRelateSetId(String lantopFieldRelateSetId) throws Exception {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("DELETE FROM LANTOP_CONDITION_SET T\n");
		sbSql.append(" WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + lantopFieldRelateSetId + "'");
		jdbcAvicit.getJdbcTemplate().execute(sbSql.toString());
		SysLogUtil.log("字段对应设置模块", "删除节点条件设置记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
	}

	/**
	 * 更新字段对应设置表SQL部分数据
	 * 
	 * @author 朱锋
	 * @param checkNodeIds
	 * @param temId
	 * @param list
	 * @param sysId
	 *            void
	 * @throws
	 */
	@Override
	public void updateRelateSetSqlByList(String checkNodeIds, String temId, List<Map<String, String>> list, String sysId) throws Exception {
		String[] sourceIds = checkNodeIds.split(";");
		if (sourceIds.length == 1) {
			// 如果只有一个源表，那么更新单表过滤条件
			String sourceTableId = sourceIds[0];// 源表ID
			// 获取字段对应设置信息ID
			String lantopFieldRelateSetId = this.getRelateSetIdByIds(sourceTableId, temId, "", sysId, "source");
			String conditionEn = "";
			String conditionCn = "";
			if (list.size() > 0) {
				conditionEn = list.get(0).get("conditionEn");
				conditionCn = list.get(0).get("conditionCn");
			}
			StringBuffer sbSql = new StringBuffer();
			sbSql.append("UPDATE LANTOP_FIELD_RELATE_SET T\n");
			sbSql.append("   SET T.SIGN_WHERE_SQL = '" + conditionEn + "', T.WHERE_SQL_CN = '" + conditionCn + "'\n");
			sbSql.append(" WHERE T.ID = '" + lantopFieldRelateSetId + "'");
			this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
			SysLogUtil.log("字段对应设置模块", "更新字段对应设置记录", OpType.update, OpResult.success, LogBaseFactory.getLogBase());
		} else if (sourceIds.length > 1) {
			// 如果有多个源表，那么更新批量过滤条件
			for (int i = 0; i < sourceIds.length; i++) {
				String sourceTableId = sourceIds[i];
				// 获取字段对应设置信息ID
				String lantopFieldRelateSetId = this.getRelateSetIdByIds(sourceTableId, temId, "", sysId, "source");
				String conditionEn = "";
				String conditionCn = "";
				if (list.size() > 0) {
					conditionEn = list.get(i).get("conditionEn");
					conditionCn = list.get(i).get("conditionCn");
				}
				StringBuffer sbSql = new StringBuffer();
				sbSql.append("UPDATE LANTOP_FIELD_RELATE_SET T\n");
				sbSql.append("   SET T.BATCH_WHERE_SQL = '" + conditionEn + "', T.WHERE_SQL_CN = '" + conditionCn + "'\n");
				sbSql.append(" WHERE T.ID = '" + lantopFieldRelateSetId + "'");
				this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
				SysLogUtil.log("字段对应设置模块", "更新字段对应设置记录", OpType.update, OpResult.success, LogBaseFactory.getLogBase());
			}
		}
	}

	/**
	 * 根据字段对应设置ID获取源表ID
	 * 
	 * @author 朱锋
	 * @param id
	 * @return
	 * @throws Exception
	 *             String
	 * @throws
	 */
	@Override
	public String getSourceTabIdByRetaleIdSetId(String id) throws Exception {
		String soucreTabId = "";
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT T.SOURCE_TABLE_ID FROM LANTOP_FIELD_RELATE_SET T WHERE T.ID = '" + id + "'");
		List<Object> list = this.hibernateDao.findBySQL(sbSql.toString());
		if (list.size() != 0) {
			soucreTabId = String.valueOf(list.get(0));
		}
		return soucreTabId;
	}

	/**
	 * 根据字段对应设置主表ID获取条件设置信息
	 * 
	 * @author 朱锋
	 * @param relateSetId
	 * @return List<Map<String,Object>>
	 * @throws
	 */
	@Override
	public List<Map<String, Object>> getConditionByRetaleIdSetId(String relateSetId) throws Exception {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT T.ID,\n");
		sbSql.append("       T.LEFT_BRACKET,\n");
		sbSql.append("       T.FIELD_NAME_EN,\n");
		sbSql.append("       T.FIELD_NAME_CN,\n");
		sbSql.append("       T.OPER_CHARACTER,\n");
		sbSql.append("       T.FILED_VALUE,\n");
		sbSql.append("       T.RIGHT_BRACKET,\n");
		sbSql.append("       T.LOGIC_OPER\n");
		sbSql.append("  FROM LANTOP_CONDITION_SET T\n");
		sbSql.append(" WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + relateSetId + "'");
		return jdbcAvicit.queryForMap(sbSql.toString(), null);
	}

	/**
	 * 获取选择库表树NODES
	 * 
	 * @author 朱锋
	 * @param id
	 * @param
	 * @return
	 * @throws Exception
	 *             List<TreeNode>
	 * @throws
	 */
	@Override
	public List<TreeNode> selectTableFindTreeNodesByParentId(String id, String type, String flag, String sOrt, String orgId) {
		// 递归一层减一层
		List<TreeNode> nodes = new ArrayList<TreeNode>();
		// 查询业务数据
		List<Map<String, Object>> childTreeList = selectTableFindNodesByParentId(id, orgId);
		// 将业务数据格式转换成esayui树形格式
		for (Map<String, Object> row : childTreeList) {
			String rid = (String) row.get("ID");
			orgId = (String) row.get("ORG_ID");
			String rname = (String) row.get("ARCHIVE_CLASS_NODE_NAME");
			String nodeType = (String) row.get("ARCHIVE_CLASS_NODE_TYPE");
			String archiveType = (String) row.get("ARCHIVE_TYPE");
			String parentId = (String) row.get("PARENT_ID");
			BigDecimal tgtCount = BigDecimal.valueOf(Long.valueOf(row.get("MAP_SET_TGT_COUNT") + ""));// 一般表对应数量
			BigDecimal tgtPubCount = BigDecimal.valueOf(Long.valueOf(row.get("MAP_SET_TGT_PUB_COUNT") + ""));// 公共字段表对应数量
			String mapSetTgtCount = tgtCount.toString();
			String mapSetTgtPubCount = tgtPubCount.toString();
			TreeNode node = new TreeNode();
			node.setId(rid);
			if ("add".equals(flag)) {
				if ("Y".equals(type)) { // 真实表
					if ("0".equals(mapSetTgtCount) || "s".equals(sOrt)) {
						node.setText(rname);
					} else {
						node.setText("<font color='red'>" + rname + "</font>");
					}
				} else { // 虚拟表
					if ("0".equals(mapSetTgtPubCount)) {
						node.setText(rname);
					} else {
						node.setText("<font color='red'>" + rname + "</font>");
					}
				}
			} else {
				node.setText(rname);
			}
			// 根据节点类型,设置节点图标
			if (null != parentId && "ROOT".equals(parentId)) { // 根节点
				node.setIconCls("icon-im-newnorg");
			} else if (null != archiveType && "-1".equals(archiveType)) { // 档案类型
																			// -1
																			// 物理表
				node.setIconCls("icon-im-newntable");
			} else if (null != archiveType && "0".equals(archiveType)) { // 0
																			// 模版文件夹
				node.setIconCls("icon-im-newnmodulefile");
			} else if (null != archiveType && "1".equals(archiveType)) { // 1
																			// 标识文件
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "2".equals(archiveType)) { // 2 档案
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "3".equals(archiveType)) { // 3 专题库
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "4".equals(archiveType)) { // 4 多媒体
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "5".equals(archiveType)) { // 5
																			// 项目归档计划
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "6".equals(archiveType)) { // 6 管务
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "7".equals(archiveType)) { // 7 其他
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != archiveType && "10".equals(archiveType)) { // 10
																			// 虚拟表
				node.setIconCls("icon-im-newnmodulelib");
			} else if (null != nodeType && "1".equals(nodeType)) { // 节点类型 1 文件夹
				node.setIconCls("icon-im-newnmodulefile");
			} else if (null != nodeType && "2".equals(nodeType)) { // 数据节点
				node.setIconCls("icon-im-newntable");
			}
			node.setAttributes(row);
			node.set_parentId((String) row.get("PARENT_ID"));
			BigDecimal childCount2 = BigDecimal.valueOf(Long.valueOf(row.get("CHILD_COUNT") + ""));
			if (null != childCount2 && childCount2.longValue() > 0) {
				List<TreeNode> cnodes = selectTableFindTreeNodesByParentId(rid, type, flag, sOrt, orgId);
				node.getChildren().addAll(cnodes);
				node.setState("open");
			} else {
				node.setState("open");
			}
			nodes.add(node);
		}
		return nodes;
	}

	/**
	 * 获取子节点
	 * 
	 * @param parentId
	 * @return
	 */
	public List<Map<String, Object>> selectTableFindNodesByParentId(String parentId, String orgId) {
		Assert.notNull(parentId, "参数不能为空！");
		StringBuffer sbSql = new StringBuffer();
		if (null != parentId && "ROOT".equals(parentId)) { // 根节点
			sbSql.append("SELECT D3.* FROM ( ");
			sbSql.append("SELECT (SELECT COUNT(*)\n");
			sbSql.append("          FROM LANTOP_ARCHIVE_TAB_CLASS_V D2\n");
			sbSql.append("         WHERE D2.PARENT_ID = D1.ID) CHILD_COUNT,\n");
			sbSql.append("       D1.*\n");
			sbSql.append("  FROM LANTOP_ARCHIVE_TAB_CLASS_V D1\n");
			sbSql.append(" WHERE D1.PARENT_ID = ? \n");
			sbSql.append(") D3 LEFT JOIN SYS_ORG_V  SOV ON  D3.ORG_ID=SOV.ID  WHERE  VALID_FLAG='1' AND D3.ORG_ID= ? ");
			sbSql.append(" ORDER BY D3.CREATION_DATE, D3.NODE_SORT_NUMBER");
			return this.jdbcTemplate.queryForList(sbSql.toString(),parentId,orgId);
		}else {
			sbSql.append("SELECT (SELECT COUNT(*)\n");
			sbSql.append("          FROM LANTOP_ARCHIVE_TAB_CLASS_V D2\n");
			sbSql.append("         WHERE D2.PARENT_ID = D1.ID) CHILD_COUNT,\n");
			sbSql.append("       D1.*\n");
			sbSql.append("  FROM LANTOP_ARCHIVE_TAB_CLASS_V D1\n");
			sbSql.append(" WHERE D1.PARENT_ID = ? AND ORG_ID = ?\n");
			sbSql.append(" ORDER BY D1.CREATION_DATE, D1.NODE_SORT_NUMBER");
			return this.jdbcTemplate.queryForList(sbSql.toString(), parentId, orgId);
		}
	}

	/**
	 * 获取字段对应设置目标节点树
	 */
	@Override
	public List<TreeNode> findTreeNodesByParentId(String pid, String rid, String orgId) throws Exception {
		List<TreeNode> nodes = new ArrayList<TreeNode>();
		List<Map<String, Object>> childTreeList = new ArrayList<Map<String, Object>>();
		if (pid == null) {
			return null;
		}
		if (null == rid || "".equals(rid)) {
			rid = "ROOT";
		}
		// 循环查询子节点
		childTreeList = findTgtNodesByParentId(pid, rid, orgId);
		try {
			// 将业务数据格式转换成esayui树形格式，此处封装了一个TreeNodeVo节点
			for (Map<String, Object> row : childTreeList) {
				rid = (String) row.get("id");
				String rname = (String) row.get("archive_class_node_name");
				String archiveType = (String) row.get("ARCHIVE_TYPE");
				String nodeType = (String) row.get("ARCHIVE_CLASS_NODE_TYPE");
				String parentId = (String) row.get("PARENT_ID");
				TreeNode node = new TreeNode();
				node.setId(rid);
				node.setText(rname);
				// 根据节点类型,设置节点图标
				if (null != parentId && "ROOT".equals(parentId)) { // 根节点
					node.setIconCls("icon-im-newnorg");
				} else if (null != archiveType && "-1".equals(archiveType) && null == nodeType) { // 档案类型 -1 物理表
					node.setIconCls("icon-im-newntable");
				} else if (null != archiveType && "0".equals(archiveType) && null == nodeType) { // 0 模版文件夹
					node.setIconCls("icon-im-newnmodulefile");
				} else if (null != archiveType && "1".equals(archiveType) && null == nodeType) { // 1 标识文件
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "2".equals(archiveType) && null == nodeType) { // 2 档案
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "3".equals(archiveType) && null == nodeType) { // 3 专题库
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "4".equals(archiveType) && null == nodeType) { // 4 多媒体
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "5".equals(archiveType) && null == nodeType) { // 5 项目归档计划
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "6".equals(archiveType) && null == nodeType) { // 6 管务
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "7".equals(archiveType) && null == nodeType) { // 7 其他
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != archiveType && "10".equals(archiveType) && null == nodeType) { // 10 虚拟表
					node.setIconCls("icon-im-newnmodulelib");
				} else if (null != nodeType && "1".equals(nodeType)) { // 节点类型 1
																		// 文件夹
					node.setIconCls("icon-im-newnmodulefile");
				} else if (null != nodeType && "2".equals(nodeType)) { // 数据节点
					node.setIconCls("icon-im-newndatanode");
				}
				node.setAttributes(row);
				// 查询当前节点下的子节点
				List<Map<String, Object>> childNodes = findTgtNodesByParentId(pid, rid, orgId);
				if (childNodes != null && childNodes.size() > 0) {
					List<TreeNode> cnodes = findTreeNodesByParentId(pid, rid, orgId);
					node.getChildren().addAll(cnodes);
					node.setState("open");
				} else {
					node.setState("open");
				}
				nodes.add(node);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nodes;
	}

	/**
	 * 获取目标节点树数据
	 * 
	 * @param
	 * @return 子节点集合
	 */
	public List<Map<String, Object>> findTgtNodesByParentId(String id, String rid, String orgId) throws Exception {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("WITH T1 AS\n");
		sbSql.append(" (SELECT T.ARCHIVE_CLASS_NODE_NAME,\n");
		sbSql.append("         T.ARCHIVE_CLASS_NODE_CODE,\n");
		sbSql.append("         T.ARCHIVE_TYPE,\n");
		sbSql.append("         T.ID,\n");
		sbSql.append("         SYS_CONNECT_BY_PATH(T.ID, '/') PH,\n");
		sbSql.append("         T.PARENT_ID\n");
		sbSql.append("    FROM LANTOP_ARCHIVE_CLASS T\n");
		sbSql.append("  CONNECT BY PRIOR T.ID = T.PARENT_ID\n");
		sbSql.append("   START WITH T.PARENT_ID = 'ROOT'),\n");
		sbSql.append("T2 AS\n");
		sbSql.append(" (SELECT * FROM T1 WHERE T1.ID = '" + id + "'),\n");
		sbSql.append("T3 AS\n");
		sbSql.append(" (SELECT T1.*\n");
		sbSql.append("    FROM T1, T2\n");
		sbSql.append("   WHERE INSTR(T2.PH, T1.ID) > 0\n");
		sbSql.append("  UNION\n");
		sbSql.append("  SELECT T1.* FROM T1, T2 WHERE INSTR(T1.PH, T2.PH) > 0),\n");
		sbSql.append("T4 AS\n");
		sbSql.append(" (SELECT LAC.* FROM T3, LANTOP_ARCHIVE_CLASS LAC WHERE LAC.ID = T3.ID)\n");
		sbSql.append("SELECT *\n");
		sbSql.append("  FROM T4\n");
		sbSql.append(" WHERE T4.PARENT_ID = '" + rid + "' AND T4. ARCHIVE_CLASS_NODE_CODE NOT IN ('BMWJ', 'GRWJ')");
		return this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
	}

	/***
	 * 根据当前节点ID查询parentId
	 * 
	 * @param id
	 * @return
	 * @throws Exception
	 */
	@Override
	public String findParentIdById(String id) throws Exception {
		String sql = "SELECT T.PARENT_ID FROM LANTOP_ARCHIVE_TAB_CLASS_V T WHERE T.ID = '" + id + "'";
		List<String> parentIdList = this.hibernateDao.findBySQL(sql);
		if (parentIdList != null && parentIdList.size() > 0) {
			return (String) parentIdList.get(0);
		} else {
			return null;
		}
	}

	/***
	 * 验证该表是否存在字段对应设置信息 （0-正常 1-没有字段对应设置主表信息 2-没有字段对应设置子表信息）
	 * 
	 * @param tableId
	 * @return
	 * @throws Exception
	 */
	@Override
	public int checkMapSetItem(String tableId) throws Exception {
		int num = 0;// 返回值
		String relateSetId = "";// 字段对应设置主表ID
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT T.ID\n");
		sbSql.append("  FROM LANTOP_FIELD_RELATE_SET T\n");
		sbSql.append(" WHERE T.SOURCE_TABLE_ID = '" + tableId + "'\n");
		sbSql.append("   AND T.TGT_TABLE_NAME = 'LANTOP_PUB_FIELD'");
		List<Object> list = this.hibernateDao.findBySQL(sbSql.toString());
		if (list.size() != 0) {
			relateSetId = String.valueOf(list.get(0));
		} else {
			num = 1;
			return num;
		}

		StringBuffer sbSqlItem = new StringBuffer();
		sbSqlItem.append("SELECT T.ID\n");
		sbSqlItem.append("  FROM LANTOP_FIELD_RELATE_SET_ITEM T\n");
		sbSqlItem.append(" WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + relateSetId + "'");
		List<Object> listItem = this.hibernateDao.findBySQL(sbSqlItem.toString());
		if (listItem.size() == 0) {
			num = 2;
			return num;
		}
		return num;
	}

	/**
	 * 根据tabId和colId删除字段对应设置信息并更新大试图
	 * @author 郭亮
	 * @param param
	 * 				p1: key:tableId
	 * 				p2: key:colName
	 * 				p3: key:orgId
	 * @throws Exception void
	 */
	@Override
	public int deleteRelateSetItemBytabIdAndColId(Map<String, String> param) throws Exception {
		int deletNum = 0;
		// 1、根据tableId获取LANTOP_FIELD_RELATE_SET_ID
		String relateSetSql = "SELECT T.ID FROM LANTOP_FIELD_RELATE_SET T WHERE T.SOURCE_TABLE_ID ='" + param.get("tableId") + "' OR T.TGT_TABLE_ID = '" + param.get("tableId")
				+ "'";
		List<String> relateSetIdList = this.hibernateDao.findBySQL(relateSetSql);
		if (relateSetIdList.size() > 0) {
			for (String str : relateSetIdList) {
				String colNames = param.get("colName");
				if (!"".equals(colNames)) {
					String[] colNameArr = colNames.split(",");
					for (String name : colNameArr) {
						// 2、根据字段英文名和LANTOP_FIELD_RELATE_SET_ID删除配置信息
						String delSql = "DELETE FROM LANTOP_FIELD_RELATE_SET_ITEM T WHERE T.LANTOP_FIELD_RELATE_SET_ID = '" + str + "' AND (T.SOURCE_FIELD_NAME_EN = '" + name
								+ "' OR T.TGT_FIELD_NAME_EN = '" + name + "')";
						deletNum = deletNum + this.jdbcTemplate.update(delSql);
					}
					this.jdbcAvicit.getJdbcTemplate().execute("commit");
					SysLogUtil.log("库结构定义模块", "删除实体表列记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
				}
			}
			// 调用存储过程更新或建立大视图
			ArrayList<Object> params = new ArrayList<Object>();
			params.add(0, (String) param.get("orgId"));
			String orgCode = (String) param.get("orgCode");
			String tempOrgCode = orgCode;
			if (orgCode.length() > 16) {
				tempOrgCode = orgCode.substring(orgCode.length() - 16);
			}
			params.add(1, tempOrgCode);
			lantopFieldMapSetManagerDao.callProcedure("LANTOP_DATA_P.LANTOP_DATA_V", params);
			SysLogUtil.log("档案业务模块", "更新档案大视图", OpType.update, OpResult.success, LogBaseFactory.getLogBase());
		}
		return deletNum;
	}
	
	@Override
    public List<Map<String, Object>> getPublicFieldInfo() throws Exception {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("WITH T1 AS (\n");
		sbSql.append("SELECT\n");
		sbSql.append("       LAC.ID,\n");
		sbSql.append("       LAC.ARCHIVE_CLASS_NODE_CODE,\n");
		sbSql.append("       LAC.ARCHIVE_CLASS_NODE_NAME,\n");
		sbSql.append("       LAC.PARENT_ID,\n");
		sbSql.append("       LAC.ARCHIVE_TYPE,\n");
		sbSql.append("       LAC.ARCHIVE_CLASS_NODE_TYPE,\n");
		sbSql.append("       LAC.IS_VISIBLE,\n");
		sbSql.append("       LAC.NODE_SORT_NUMBER,\n");
		sbSql.append("       LAC.RELAT_WORKFLOW,\n");
		sbSql.append("       LAC.TABLE_LAYER,\n");
		sbSql.append("       LAC.ARCHIVE_CLASS_NODE_PATH,\n");
		sbSql.append("       LAC.REMARK,\n");
		sbSql.append("       LAC.ORG_ID,\n");
		sbSql.append("       LAC.DEPT_ID,\n");
		sbSql.append("       LAC.SYS_ID,\n");
		sbSql.append("       LAC.SECRET_LEVEL,\n");
		sbSql.append("       LAC.SYS_APPLICATION_ID,\n");
		sbSql.append("       LAC.CREATED_BY,\n");
		sbSql.append("       LAC.CREATION_DATE,\n");
		sbSql.append("       LAC.LAST_UPDATED_BY,\n");
		sbSql.append("       LAC.LAST_UPDATE_DATE,\n");
		sbSql.append("       LAC.LAST_UPDATE_IP,\n");
		sbSql.append("       LAC.VERSION,\n");
		sbSql.append("       LAC.ATTRIBUTE_01,\n");
		sbSql.append("       LAC.ATTRIBUTE_02,\n");
		sbSql.append("       LAC.ATTRIBUTE_03,\n");
		sbSql.append("       LAC.ATTRIBUTE_04,\n");
		sbSql.append("       LAC.ATTRIBUTE_05,\n");
		sbSql.append("       LAC.ATTRIBUTE_06,\n");
		sbSql.append("       LAC.ATTRIBUTE_07,\n");
		sbSql.append("       LAC.ATTRIBUTE_08,\n");
		sbSql.append("       LAC.ATTRIBUTE_09,\n");
		sbSql.append("       LAC.ATTRIBUTE_10,\n");
		sbSql.append("       LAC.ATTRIBUTE_11,\n");
		sbSql.append("       LAC.ATTRIBUTE_12,\n");
		sbSql.append("       LAC.ATTRIBUTE_13,\n");
		sbSql.append("       LAC.ATTRIBUTE_14,\n");
		sbSql.append("       LAC.ATTRIBUTE_15,\n");
		sbSql.append("       '' AS TABLE_IS_TRUE_TABLE,\n");
		sbSql.append("       '' AS TABLE_NAME\n");
		sbSql.append("  FROM LANTOP_ARCHIVE_CLASS LAC\n");
		sbSql.append(" WHERE LAC.ARCHIVE_CLASS_NODE_TYPE='' OR LAC.ARCHIVE_CLASS_NODE_TYPE IS NULL\n");
		sbSql.append("  ),\n");
		sbSql.append("  T2 AS (\n");
		sbSql.append("      SELECT * FROM T1\n");
		sbSql.append("      UNION ALL\n");
		sbSql.append("      SELECT\n");
		sbSql.append("       CT.ID,\n");
		sbSql.append("       T1.ARCHIVE_CLASS_NODE_CODE,\n");
		sbSql.append("       CT.TABLE_TITLE AS ARCHIVE_CLASS_NODE_NAME,\n");
		sbSql.append("       T1.ID AS PARENT_ID,\n");
		sbSql.append("       '-1' AS ARCHIVE_TYPE,\n");
		sbSql.append("       T1.ARCHIVE_CLASS_NODE_TYPE,\n");
		sbSql.append("       T1.IS_VISIBLE,\n");
		sbSql.append("       CTN.TAB_ORDER AS NODE_SORT_NUMBER,\n");
		sbSql.append("       T1.RELAT_WORKFLOW,\n");
		sbSql.append("       T1.TABLE_LAYER,\n");
		sbSql.append("       T1.ARCHIVE_CLASS_NODE_PATH || '/' || CT.ID AS ARCHIVE_CLASS_NODE_PATH,\n");
		sbSql.append("       T1.REMARK,\n");
		sbSql.append("       T1.ORG_ID,\n");
		sbSql.append("       T1.DEPT_ID,\n");
		sbSql.append("       T1.SYS_ID,\n");
		sbSql.append("       T1.SECRET_LEVEL,\n");
		sbSql.append("       T1.SYS_APPLICATION_ID,\n");
		sbSql.append("       T1.CREATED_BY,\n");
		sbSql.append("       T1.CREATION_DATE,\n");
		sbSql.append("       T1.LAST_UPDATED_BY,\n");
		sbSql.append("       T1.LAST_UPDATE_DATE,\n");
		sbSql.append("       T1.LAST_UPDATE_IP,\n");
		sbSql.append("       T1.VERSION,\n");
		sbSql.append("       T1.ATTRIBUTE_01,\n");
		sbSql.append("       T1.ATTRIBUTE_02,\n");
		sbSql.append("       T1.ATTRIBUTE_03,\n");
		sbSql.append("       T1.ATTRIBUTE_04,\n");
		sbSql.append("       T1.ATTRIBUTE_05,\n");
		sbSql.append("       T1.ATTRIBUTE_06,\n");
		sbSql.append("       T1.ATTRIBUTE_07,\n");
		sbSql.append("       T1.ATTRIBUTE_08,\n");
		sbSql.append("       T1.ATTRIBUTE_09,\n");
		sbSql.append("       T1.ATTRIBUTE_10,\n");
		sbSql.append("       T1.ATTRIBUTE_11,\n");
		sbSql.append("       T1.ATTRIBUTE_12,\n");
		sbSql.append("       T1.ATTRIBUTE_13,\n");
		sbSql.append("       T1.ATTRIBUTE_14,\n");
		sbSql.append("       T1.ATTRIBUTE_15,\n");
		sbSql.append("       CT.TABLE_IS_TRUE_TABLE,\n");
		sbSql.append("       CT.TABLE_NAME\n");
		sbSql.append("        FROM T1\n");
		sbSql.append("      INNER JOIN CBB_TAB_NODE CTN\n");
		sbSql.append("      ON T1.ID = CTN.NODE_ID\n");
		sbSql.append("      INNER JOIN  CBB_TABLES CT\n");
		sbSql.append("      ON CTN.TABLE_ID = CT.ID\n");
		sbSql.append("  )\n");
		sbSql.append("  SELECT * FROM T2 WHERE ARCHIVE_CLASS_NODE_CODE = 'LANTOP_PUB_FIELD'"); 
		return this.jdbcTemplate.queryForList(sbSql.toString());
	}
}
